<?php

namespace App\Http\Controllers\Home;

use App\Http\Controllers\Controller;
use DB;


class ChartsController extends Controller
{

	public function index() 
    {

		$category = DB::table('checkcategory')->select(['id','name'])->get();
		$cateArray = [];
		foreach($category as $k => $v)
		{
			$cateArray[] = $v->name;
		}
		$cateCount = DB::select("select count(company.id) as count,checkcategory.name from checkcategory
								 left join company on checkcategory.id = company.category group by checkcategory.id ");
		$cateDataArray = [];
		foreach($cateCount as $k => $v)
		{
			$temp = [];
			$temp['value'] = $v->count;
			$temp['name'] = $v->name;
			$cateDataArray[] = $temp;
		}
    	$monthday = date('t');
    	$monthArray = [];
    	$monthdata = [];
    	$min = date('Y-m-01', strtotime(date("Y-m-d")));
    	$max = date('Y-m-d', strtotime("$min +1 month -1 day"));
    	for ($i=1; $i <= $monthday; $i++) { 
    		$monthArray[] = $i; 
    		$monthdata[] = 0;
    	}
    	$sql = "SELECT
			t.created,
			count(*) AS total_num 
		FROM
			 (
				SELECT
					date_format(created, '%e') AS created
				FROM
					comcheck
				WHERE
					created BETWEEN '{$min}'
				AND '{$max}'
			) t
		GROUP BY
			t.created
		ORDER BY
			t.created";
		$data = DB::select($sql);
		
    	foreach ($data as $k => $v) {
    		$date  = $v->created - 1;
    		$monthdata[$date] = $v->total_num;
		}
        return view('charts.index')
        	->with('monthdata',json_encode($monthdata))
        	->with('cate',json_encode($cateArray,JSON_UNESCAPED_UNICODE))
        	->with('catedata',json_encode($cateDataArray,JSON_UNESCAPED_UNICODE))
			->with('monthArray',json_encode($monthArray));
    }
}
